﻿
CREATE PROCEDURE [dbo].[amix_spViewRegistru]
	-- Add the parameters for the stored procedure here
	@filtru varchar(1000), 
	@an smallint,
	@luna smallint,
	@CodUnitate smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @PerCurenta decimal(10)
	SELECT @PerCurenta = AnulCurent*100+LunaCurenta FROM AmixSetup WHERE CodUnitate=@CodUnitate
	DECLARE @Arhiva bit

	SET @Arhiva=0
	IF @PerCurenta>@An*100+@Luna
		SET @Arhiva= 1

    DECLARE @cmd varchar(8000)
    SET @cmd =
	' SELECT  *  FROM (SELECT  ar.NrInventar, ar.Denumire, ar.ContAmortizare, ar.DataIntrare, ar.DataIesire, 
                      ar.CodUnitate,  ar.ContImobilizare, ar.ContCheltuiala, ar.DurataAmortizare, ar.IDMijlocFix,
                      ar.RegimAmortizare, ar.ValoareInventar, 
                      ar.Compus,ar.StocFaptic, ar.Categorie,ar.Blocat,ar.IDStare,
                      dbo.Departamente.Nume,
                      COALESCE (aa.ValoareAmortizata,CASE WHEN YEAR(ar.PunereInFunctiune)*100 + MONTH(ar.PunereInFunctiune) < '+CAST(@An*100+@luna AS varchar(6))+' THEN ar.ValoareInventar ELSE ar.ValoareAmortizata END) AS ValoareAmortizata, 
                      COALESCE (aa.ValoareRamasa, 0) AS ValoareRamasa, COALESCE (aa.AmortizareLuna, 0) AS AmortizareLuna, COALESCE (aa.DurataNormata, 0) 
                      AS DurataNormata, aa.LuniAmortizate, COALESCE (aa.Luna, 0) AS Luna, COALESCE (aa.An, 0) AS An, 
                      COALESCE (aa.Blocat, 0) AS AmortizareBlocata,
                      dbo.AmixTipStare.Denumire AS Stare, 
                      dbo.AmixGestionari.Nume AS Gestionar, 
                      dbo.AmixCatalog.Cod,dbo.AmixCatalog.Grupa,ContTransferForSuperior, ValoareIntrataTransfer,j.Codificare,ar.ContTransfer,ar.IDSursaFinantare,
                      ss.Denumire as SursaFinantare
		FROM  '
		
		IF @Arhiva = 1
			SET @cmd = @cmd + '(SELECT *  FROM dbo.AmixRegistruBK WHERE Luna='+CAST(@Luna as varchar(4)) + ' AND An=' + CAST(@an as varchar(4)) + ')'
		ELSE
			SET @cmd = @cmd + 'dbo.AmixRegistru ' 
		SET @cmd = @cmd +' ar 
					  LEFT OUTER JOIN SurseFinantare ss ON ar.IDSursaFinantare=ss.IDSursaFinantare LEFT OUTER JOIN
					  (SELECT * FROM AmixAmortizare aa WHERE aa.Luna='+CAST(@Luna AS varchar(2))+' and aa.An= ' + cast(@An as varchar(4)) + ') aa  ON ar.IDMijlocFix = aa.IDMijlocFix 
                      LEFT OUTER JOIN  dbo.AmixCatalog ON ar.IDClasificare = dbo.AmixCatalog.IDClasificare 
                      LEFT OUTER JOIN dbo.Departamente ON ar.IDDepartament = dbo.Departamente.IDDepartament 
                      LEFT OUTER JOIN dbo.AmixTipStare ON ar.IDStare = dbo.AmixTipStare.IDStare 
					  LEFT OUTER JOIN dbo.Jurnale j ON ar.IDJurnal = j.IDJurnal 
                      LEFT OUTER JOIN dbo.AmixGestionari ON ar.IDGestionar = dbo.AmixGestionari.IDGestionar) fs '
     IF @filtru <> ''
		SET @cmd = @cmd + ' WHERE ' + @filtru
     EXEC(@cmd) 
 
END